--    Author    : MAYANTING
--    Name      : ADM.CUBE_SUST_DLMG_DEPB.HQL
--    Functions : 多维存款余额(元)
--    Purpose   :
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2018-06-12  MAYANTING           1.CREATE THE PROCEDURE
--
INSERT OVERWRITE TABLE ADM.CUBE_SUST_DLMG_DEPB PARTITION (DATA_DATE = '#V_DATA_DATE#')
 SELECT  ORG.ORG_CODE_1                                                                     AS FIN_ORG_NO_CODE_1    -- 一级金融机构代码
        ,ORG.ORG_DSCR_1                                                                     AS FIN_ORG_NO_NAME_1    -- 一级金融机构描述
        ,ORG.ORG_CODE_2                                                                     AS FIN_ORG_NO_CODE_2    -- 二级金融机构代码
        ,ORG.ORG_DSCR_2                                                                     AS FIN_ORG_NO_NAME_2    -- 二级金融机构描述
        ,ORG.ORG_CODE_3                                                                     AS FIN_ORG_NO_CODE_3    -- 三级金融机构代码
        ,ORG.ORG_DSCR_3                                                                     AS FIN_ORG_NO_NAME_3    -- 三级金融机构描述
        ,AREA.AREA_CODE_1                                                                   AS FIN_ORG_DIST_CODE_1  -- 一级金融机构地区代码
        ,AREA.AREA_DSCR_1                                                                   AS FIN_ORG_DIST_NAME_1  -- 一级金融机构地区描述
        ,AREA.AREA_CODE_3                                                                   AS FIN_ORG_DIST_CODE_2  -- 二级金融机构地区代码
        ,AREA.AREA_DSCR_3                                                                   AS FIN_ORG_DIST_NAME_2  -- 二级金融机构地区描述
        ,AREA.AREA_CODE_4                                                                   AS FIN_ORG_DIST_CODE_3  -- 三级金融机构地区代码
        ,AREA.AREA_DSCR_4                                                                   AS FIN_ORG_DIST_NAME_3  -- 三级金融机构地区描述
        ,CUR.CURRENCY_ID                                                                    AS CCY_CODE             -- 贷款币种代码
        ,CUR.CURRENCY_DSCR                                                                  AS CCY_NAME             -- 贷款币种描述
        ,CUST.CUST_TYPE_ID                                                                  AS CLIENT_TYPE_CODE     -- 客户类型代码
        ,CUST.CUST_TYPE_DSCR                                                                AS CLIENT_TYPE_NAME     -- 客户类型描述
        ,PRO.DEPT_PRODUCT_ID_1                                                              AS DEPOSIT_TYPE_CODE_1  -- 一级存款产品类别代码
        ,PRO.DEPT_PRODUCT_DSCR_1                                                            AS DEPOSIT_TYPE_NAME_1  -- 一级存款产品类别描述
        ,PRO.DEPT_PRODUCT_ID_2                                                              AS DEPOSIT_TYPE_CODE_2  -- 二级存款产品类别代码
        ,PRO.DEPT_PRODUCT_DSCR_2                                                            AS DEPOSIT_TYPE_NAME_2  -- 二级存款产品类别描述
        ,VDATE.YEAR_NAME                                                                    AS VALUE_DATE_Y         -- 存款协议起始日期-年
        ,CONCAT(VDATE.YEAR_NAME,VDATE.QUARTER_NAME)                                         AS VALUE_DATE_Q         -- 存款协议起始日期-季
        ,CONCAT(VDATE.YEAR_NAME,SUBSTR(VDATE.PK_DATE,5,2),SUBSTR(VDATE.MONTH_NAME,-1,1))    AS VALUE_DATE_M         -- 存款协议起始日期-月
        ,MDATE.YEAR_NAME                                                                    AS MATURITY_DATE_Y      -- 存款协议到期日期-年
        ,CONCAT(MDATE.YEAR_NAME,MDATE.QUARTER_NAME)                                         AS MATURITY_DATE_Q      -- 存款协议到期日期-季
        ,CONCAT(MDATE.YEAR_NAME,SUBSTR(MDATE.PK_DATE,5,2),SUBSTR(MDATE.MONTH_NAME,-1,1))    AS MATURITY_DATE_M      -- 存款协议到期日期-月
        ,LV.FIX_FLOAT_TYPE_ID                                                               AS IF_FLOAT_CODE        -- 利率是否固定代码
        ,LV.FIX_FLOAT_TYPE_DSCR                                                             AS IF_FLOAT_NAME        -- 利率是否固定描述
        ,SUM(COALESCE(DEP.ACTUAL_BAL,0))                                                    AS ACTUAL_BAL           -- 存款余额
        ,SUM(COALESCE(DEP.WSUM_BAL  ,0))                                                    AS WSUM_BAL             -- 余额加权值
 FROM EDW.DS_DEPB_SUM DEP
 -- 与机构关联
 LEFT JOIN DIMENSION.T_ORG_BIZ_LVL ORG ON DEP.FIN_ORG_NO=ORG.ORG_CODE_4 AND ORG.IS_ACTIVE='1' AND '#V_DATA_DATE#' BETWEEN ORG.START_DATE AND ORG.END_DATE
 -- 与地区表最细级关联
 LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON DEP.FIN_ORG_NO=REAL.ORG_ID AND '#V_DATA_DATE#' BETWEEN REAL.START_DATE AND REAL.END_DATE
 LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '#V_DATA_DATE#' BETWEEN AREA.START_DATE AND AREA.END_DATE
 -- 关联币种
 LEFT JOIN DIMENSION.DIM_CURRENCY CUR ON DEP.CCY=CUR.CURRENCY_ID AND CUR.IS_VALID='1' AND '#V_DATA_DATE#' BETWEEN CUR.START_DATE AND CUR.END_DATE
 -- 关联客户类型
 LEFT JOIN DIMENSION.DIM_CUSTOMER_TYPE CUST ON DEP.CLIENT_TYPE=CUST.CUST_TYPE_ID AND CUST.IS_VALID='1' AND '#V_DATA_DATE#' BETWEEN CUST.START_DATE AND CUST.END_DATE
 -- 关联存款产品类别
 LEFT JOIN DIMENSION.DIM_DEPOSIT_PRODUCT PRO ON DEP.DEPOSIT_TYPE=PRO.DEPT_PRODUCT_ID AND PRO.IS_VALID='1' AND '#V_DATA_DATE#' BETWEEN PRO.START_DATE AND PRO.END_DATE
 -- 存款协议起始日期 关联日期维度表
 LEFT JOIN DIMENSION.DIM_DATE_DETAIL VDATE ON CONCAT(SUBSTR(DEP.VALUE_DATE,1,4),SUBSTR(DEP.VALUE_DATE,6,2),SUBSTR(DEP.VALUE_DATE,9,2))=VDATE.PK_DATE
 -- 存款协议到期日期 关联日期维度表
 LEFT JOIN DIMENSION.DIM_DATE_DETAIL MDATE ON CONCAT(SUBSTR(DEP.MATURITY_DATE,1,4),SUBSTR(DEP.MATURITY_DATE,6,2),SUBSTR(DEP.MATURITY_DATE,9,2))=MDATE.PK_DATE
  -- 关联利率是否固定
 LEFT JOIN DIMENSION.DIM_RATE_FIX_FLOAT LV ON DEP.IF_FLOAT=LV.FIX_FLOAT_TYPE_ID AND LV.IS_VALID='1' AND '#V_DATA_DATE#' BETWEEN LV.START_DATE AND LV.END_DATE
 GROUP BY   ORG.ORG_CODE_1
           ,ORG.ORG_DSCR_1
           ,ORG.ORG_CODE_2
           ,ORG.ORG_DSCR_2
           ,ORG.ORG_CODE_3
           ,ORG.ORG_DSCR_3
           ,AREA.AREA_CODE_1
           ,AREA.AREA_DSCR_1
           ,AREA.AREA_CODE_3
           ,AREA.AREA_DSCR_3
           ,AREA.AREA_CODE_4
           ,AREA.AREA_DSCR_4
           ,CUR.CURRENCY_ID
           ,CUR.CURRENCY_DSCR
           ,CUST.CUST_TYPE_ID
           ,CUST.CUST_TYPE_DSCR
           ,PRO.DEPT_PRODUCT_ID_1
           ,PRO.DEPT_PRODUCT_DSCR_1
           ,PRO.DEPT_PRODUCT_ID_2
           ,PRO.DEPT_PRODUCT_DSCR_2
           ,VDATE.YEAR_NAME
           ,CONCAT(VDATE.YEAR_NAME,VDATE.QUARTER_NAME)
           ,CONCAT(VDATE.YEAR_NAME,SUBSTR(VDATE.PK_DATE,5,2),SUBSTR(VDATE.MONTH_NAME,-1,1))
           ,MDATE.YEAR_NAME
           ,CONCAT(MDATE.YEAR_NAME,MDATE.QUARTER_NAME)
           ,CONCAT(MDATE.YEAR_NAME,SUBSTR(MDATE.PK_DATE,5,2),SUBSTR(MDATE.MONTH_NAME,-1,1))
           ,LV.FIX_FLOAT_TYPE_ID
           ,LV.FIX_FLOAT_TYPE_DSCR;